package com.lcc.lynx.excel;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.net.URLEncodeUtil;
import cn.idev.excel.FastExcel;
import com.lcc.lynx.common.exception.e.ExcelExportException;
import com.lcc.lynx.common.utils.AssertUtil;
import com.lcc.lynx.common.utils.ReflectUtil;
import com.lcc.lynx.common.utils.StrUtil;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.lang.reflect.Field;
import java.util.*;

/**
 * @author lcc
 * @date 2025/1/23 9:56
 */
public class ExcelUtil {

    /**
     * 导出Excel
     * @param list
     * @param fileName
     * @param response
     * @throws IOException
     * @author lcc
     * @date 2025/7/31 17:09
     */
    public static void down(List<IExcel> list, String fileName, HttpServletResponse response) throws IOException {
        List<?> listDate = list.stream().map(IExcel::toExcel).toList();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + URLEncodeUtil.encode(fileName));
        FastExcel.write(response.getOutputStream(), listDate.get(0).getClass())
                .sheet("sheet1")
                .doWrite(listDate);
    }

    /**
     * 根据标题读取Excel， 根据标题自动匹配,字段需要使用@ExcelProperty注解
     *
     * @param file 文件
     * @author lcc
     * @date 2025/3/31 17:09
     */
    public static <T> List<T> readByTitle(MultipartFile file, Class<T> clazz) throws IOException {
        validateFile(file);
        return FastExcel.read(file.getInputStream())
                .head(clazz)
                .sheet()
                .doReadSync();
    }

    private static void validateFile(MultipartFile file) {
        AssertUtil.isTrue(file != null, "请选择文件");
        AssertUtil.isTrue(file.getOriginalFilename().endsWith(".xls")
                || file.getOriginalFilename().endsWith(".xlsx"), "文件格式错误仅支持(.xls, .xlsx)");

    }

    /**
     * 顺序读取Excel， 实体中的字段要和excel的列顺序一一对应
     *
     * @param file 文件
     * @author lcc
     * @date 2025/3/31 17:09
     */
    public static <T> List<T> readByOrder(MultipartFile file, Class<T> clazz) throws IOException {
        validateFile(file);
        Workbook wb = file.getOriginalFilename().endsWith(".xls")
                ? new HSSFWorkbook(file.getInputStream()) : new XSSFWorkbook(file.getInputStream());
        Sheet sheet = wb.getSheetAt(0);
        List<Field> fields = ReflectUtil.getFields(clazz);
        List<T> list = new ArrayList<>();
        for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) {
            int cellNum = 0;
            list.add(ReflectUtil.newInstance(clazz));
            for (Field field : fields) {
                Cell cell = sheet.getRow(i).getCell(cellNum++);
                String value = getStringCellValue(cell);
                if (StrUtil.isEmpty(value)) continue;
                try {
                    if (field.getType() == Date.class) {
                        ReflectUtil.setFieldValue(list.get(list.size() - 1), field, cell.getCellType() == CellType.STRING
                                ? DateUtil.parse(value) : cell.getDateCellValue());
                    } else {
                        ReflectUtil.setFieldValue(list.get(list.size() - 1), field, value);
                    }
                } catch (Exception e) {
                    throw new ExcelExportException("第" + (i + 1) + "行,第" + getColumnName(cellNum - 1) + "列数据转换错误: "
                            + (field.getType() == Date.class ? "请把单元格设置为日期格式或者文本格式(yyyy-mm-dd 或 yyyy-MM-dd HH:mm:ss)" : e.getMessage()));
                }
            }
        }
        return list;
    }

    /**
     * 根据列索引获取Excel列名(A,B,C...AA,AB等)
     *
     * @param columnIndex 列索引(0-based)
     * @return 对应的列名(如A, B, ...AA, AB)
     */
    public static String getColumnName(int columnIndex) {
        if (columnIndex < 0) {
            throw new IllegalArgumentException("列索引不能为负数");
        }

        StringBuilder columnName = new StringBuilder();
        while (columnIndex >= 0) {
            int remainder = columnIndex % 26;
            columnName.insert(0, (char) ('A' + remainder));
            columnIndex = (columnIndex / 26) - 1;
        }
        return columnName.toString();
    }

    /**
     * 获取单元格数据内容为字符串类型的数据
     *
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    public static String getStringCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        return switch (cell.getCellType()) {
            case STRING -> cell.getStringCellValue();
            case NUMERIC -> {
                String s = String.valueOf(cell.getNumericCellValue());
                yield s.endsWith(".0") ? s.substring(0, s.length() - 2) : s;
            }
            case BOOLEAN -> String.valueOf(cell.getBooleanCellValue());
            default -> "".trim();
        };
    }

    public static String getFileName(HttpServletRequest request) {
        String fileName = request.getHeader("export-excel");
        if (StrUtil.isEmpty(fileName)) {
            fileName = request.getParameter("exportExcel");
        }
        if (StrUtil.isEmpty(fileName)) {
            fileName = "未命名.xlsx";
        }
        return fileName;
    }

}
